{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fd90793a-8d1f-4687-b8e6-5868f2ef58a5",
   "metadata": {},
   "source": [
    "# Pandas实践\n",
    "## 1、文件的读取和查询\n",
    "### 1.1、文件的读取和统计基础"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d3589fbb-d4f0-43aa-a6e3-b1fb264a98f1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "****************************************************************************************************\n",
      "(121, 17)\n",
      "****************************************************************************************************\n",
      "Index(['student_id', 'name', 'gender', 'class', 'age', 'register_date', 'city',\n",
      "       'club', 'Chinese', 'Math', 'English', 'Physics', 'Chemistry', 'Biology',\n",
      "       'absence_count', 'height_cm', 'weight_kg'],\n",
      "      dtype='object')\n",
      "****************************************************************************************************\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 121 entries, 0 to 120\n",
      "Data columns (total 17 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   student_id     121 non-null    int64  \n",
      " 1   name           121 non-null    object \n",
      " 2   gender         121 non-null    object \n",
      " 3   class          121 non-null    object \n",
      " 4   age            121 non-null    int64  \n",
      " 5   register_date  121 non-null    object \n",
      " 6   city           101 non-null    object \n",
      " 7   club           102 non-null    object \n",
      " 8   Chinese        115 non-null    float64\n",
      " 9   Math           112 non-null    float64\n",
      " 10  English        121 non-null    float64\n",
      " 11  Physics        121 non-null    float64\n",
      " 12  Chemistry      121 non-null    float64\n",
      " 13  Biology        121 non-null    float64\n",
      " 14  absence_count  120 non-null    float64\n",
      " 15  height_cm      121 non-null    float64\n",
      " 16  weight_kg      121 non-null    float64\n",
      "dtypes: float64(9), int64(2), object(6)\n",
      "memory usage: 16.2+ KB\n",
      "None\n",
      "****************************************************************************************************\n",
      "         student_id         age     Chinese        Math  ...     Biology  absence_count   height_cm   weight_kg\n",
      "count    121.000000  121.000000  115.000000  112.000000  ...  121.000000     120.000000  121.000000  121.000000\n",
      "mean   23060.090909   10.330579   80.170435   80.370536  ...   80.383471       2.008333  154.518182   41.542149\n",
      "std       34.930884    0.934420    8.977525   11.157364  ...    7.509165       1.356564    7.155685    6.052806\n",
      "min    23001.000000    9.000000   58.800000   54.600000  ...   59.800000       0.000000  139.200000   19.100000\n",
      "25%    23030.000000   10.000000   74.200000   71.875000  ...   75.500000       1.000000  150.100000   38.600000\n",
      "50%    23060.000000   10.000000   79.300000   81.900000  ...   80.800000       2.000000  154.100000   41.100000\n",
      "75%    23090.000000   11.000000   85.500000   88.125000  ...   85.400000       3.000000  159.900000   45.500000\n",
      "max    23120.000000   12.000000  105.000000  105.100000  ...  103.100000       6.000000  168.900000   55.600000\n",
      "\n",
      "[8 rows x 11 columns]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23001</td>\n",
       "      <td>褚超琪</td>\n",
       "      <td>83.2</td>\n",
       "      <td>104.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23002</td>\n",
       "      <td>孙明丽</td>\n",
       "      <td>74.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23003</td>\n",
       "      <td>冯鑫芸</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23004</td>\n",
       "      <td>孔凯霖</td>\n",
       "      <td>76.7</td>\n",
       "      <td>69.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23005</td>\n",
       "      <td>杨旭豪</td>\n",
       "      <td>67.9</td>\n",
       "      <td>66.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23006</td>\n",
       "      <td>何涛</td>\n",
       "      <td>64.8</td>\n",
       "      <td>83.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>23007</td>\n",
       "      <td>吴萌萱</td>\n",
       "      <td>70.0</td>\n",
       "      <td>79.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>23008</td>\n",
       "      <td>何楠</td>\n",
       "      <td>75.2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23009</td>\n",
       "      <td>赵超豪</td>\n",
       "      <td>63.8</td>\n",
       "      <td>71.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>23010</td>\n",
       "      <td>华旭琪</td>\n",
       "      <td>70.8</td>\n",
       "      <td>70.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>23011</td>\n",
       "      <td>孔雯璐</td>\n",
       "      <td>90.0</td>\n",
       "      <td>64.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>23012</td>\n",
       "      <td>卫洋琪</td>\n",
       "      <td>74.2</td>\n",
       "      <td>74.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>23013</td>\n",
       "      <td>朱杰萱</td>\n",
       "      <td>67.7</td>\n",
       "      <td>75.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>23014</td>\n",
       "      <td>孙婷霖</td>\n",
       "      <td>79.6</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>23015</td>\n",
       "      <td>金博萱</td>\n",
       "      <td>76.9</td>\n",
       "      <td>80.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>23016</td>\n",
       "      <td>魏伟芸</td>\n",
       "      <td>81.4</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>23017</td>\n",
       "      <td>吕颖睿</td>\n",
       "      <td>75.8</td>\n",
       "      <td>77.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>23018</td>\n",
       "      <td>冯雯娟</td>\n",
       "      <td>77.5</td>\n",
       "      <td>84.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>23019</td>\n",
       "      <td>尤静洋</td>\n",
       "      <td>78.3</td>\n",
       "      <td>78.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>23020</td>\n",
       "      <td>韩佳怡</td>\n",
       "      <td>82.7</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>23021</td>\n",
       "      <td>杨林琪</td>\n",
       "      <td>58.8</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>23022</td>\n",
       "      <td>褚坤璐</td>\n",
       "      <td>65.6</td>\n",
       "      <td>90.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23023</td>\n",
       "      <td>钱强霖</td>\n",
       "      <td>80.3</td>\n",
       "      <td>71.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23024</td>\n",
       "      <td>冯博彤</td>\n",
       "      <td>72.3</td>\n",
       "      <td>72.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>23025</td>\n",
       "      <td>周鑫琪</td>\n",
       "      <td>78.5</td>\n",
       "      <td>87.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>23026</td>\n",
       "      <td>赵国坤</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>23027</td>\n",
       "      <td>卫博琪</td>\n",
       "      <td>77.6</td>\n",
       "      <td>84.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>23028</td>\n",
       "      <td>周鑫昕</td>\n",
       "      <td>84.5</td>\n",
       "      <td>85.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>23029</td>\n",
       "      <td>蒋宁然</td>\n",
       "      <td>83.6</td>\n",
       "      <td>56.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>23030</td>\n",
       "      <td>华颖</td>\n",
       "      <td>67.6</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>23031</td>\n",
       "      <td>周杰涵</td>\n",
       "      <td>74.2</td>\n",
       "      <td>91.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>23032</td>\n",
       "      <td>赵磊丽</td>\n",
       "      <td>73.7</td>\n",
       "      <td>86.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>23033</td>\n",
       "      <td>赵博萌</td>\n",
       "      <td>81.7</td>\n",
       "      <td>71.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>23034</td>\n",
       "      <td>卫超桐</td>\n",
       "      <td>69.6</td>\n",
       "      <td>78.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>23035</td>\n",
       "      <td>陶坤萱</td>\n",
       "      <td>92.2</td>\n",
       "      <td>92.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>23036</td>\n",
       "      <td>秦芳璐</td>\n",
       "      <td>87.5</td>\n",
       "      <td>68.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>23037</td>\n",
       "      <td>李悦博</td>\n",
       "      <td>92.1</td>\n",
       "      <td>68.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>23038</td>\n",
       "      <td>秦杰丽</td>\n",
       "      <td>81.6</td>\n",
       "      <td>73.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>23039</td>\n",
       "      <td>蒋雯萌</td>\n",
       "      <td>75.9</td>\n",
       "      <td>105.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>23040</td>\n",
       "      <td>华浩娟</td>\n",
       "      <td>95.3</td>\n",
       "      <td>94.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>23041</td>\n",
       "      <td>吕林桐</td>\n",
       "      <td>85.3</td>\n",
       "      <td>67.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>23042</td>\n",
       "      <td>钱楠博</td>\n",
       "      <td>85.8</td>\n",
       "      <td>74.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>23043</td>\n",
       "      <td>华楠桐</td>\n",
       "      <td>96.1</td>\n",
       "      <td>95.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>23044</td>\n",
       "      <td>钱萌琪</td>\n",
       "      <td>78.2</td>\n",
       "      <td>89.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>23045</td>\n",
       "      <td>沈旭玥</td>\n",
       "      <td>84.0</td>\n",
       "      <td>93.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>23046</td>\n",
       "      <td>施强洋</td>\n",
       "      <td>84.0</td>\n",
       "      <td>81.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>23047</td>\n",
       "      <td>韩宁骁</td>\n",
       "      <td>74.8</td>\n",
       "      <td>85.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>23048</td>\n",
       "      <td>尤凯茜</td>\n",
       "      <td>63.6</td>\n",
       "      <td>68.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>23049</td>\n",
       "      <td>金杰玥</td>\n",
       "      <td>79.3</td>\n",
       "      <td>80.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>23050</td>\n",
       "      <td>金凯茜</td>\n",
       "      <td>78.8</td>\n",
       "      <td>75.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    student_id   name  Chinese   Math\n",
       "0        23001    褚超琪     83.2  104.5\n",
       "1        23002    孙明丽     74.5    NaN\n",
       "2        23003   冯鑫芸       NaN   58.5\n",
       "3        23004    孔凯霖     76.7   69.7\n",
       "4        23005    杨旭豪     67.9   66.8\n",
       "5        23006     何涛     64.8   83.4\n",
       "6        23007    吴萌萱     70.0   79.9\n",
       "7        23008     何楠     75.2    NaN\n",
       "8        23009    赵超豪     63.8   71.9\n",
       "9        23010    华旭琪     70.8   70.2\n",
       "10       23011    孔雯璐     90.0   64.7\n",
       "11       23012    卫洋琪     74.2   74.9\n",
       "12       23013    朱杰萱     67.7   75.4\n",
       "13       23014    孙婷霖     79.6   57.0\n",
       "14       23015    金博萱     76.9   80.2\n",
       "15       23016    魏伟芸     81.4   88.0\n",
       "16       23017    吕颖睿     75.8   77.4\n",
       "17       23018    冯雯娟     77.5   84.2\n",
       "18       23019    尤静洋     78.3   78.9\n",
       "19       23020    韩佳怡     82.7   82.0\n",
       "20       23021    杨林琪     58.8   86.0\n",
       "21       23022    褚坤璐     65.6   90.6\n",
       "22       23023    钱强霖     80.3   71.5\n",
       "23       23024    冯博彤     72.3   72.5\n",
       "24       23025    周鑫琪     78.5   87.3\n",
       "25       23026    赵国坤      NaN   79.3\n",
       "26       23027    卫博琪     77.6   84.8\n",
       "27       23028    周鑫昕     84.5   85.4\n",
       "28       23029    蒋宁然     83.6   56.9\n",
       "29       23030     华颖     67.6   78.0\n",
       "30       23031    周杰涵     74.2   91.6\n",
       "31       23032    赵磊丽     73.7   86.8\n",
       "32       23033    赵博萌     81.7   71.9\n",
       "33       23034    卫超桐     69.6   78.7\n",
       "34       23035    陶坤萱     92.2   92.1\n",
       "35       23036   秦芳璐      87.5   68.5\n",
       "36       23037    李悦博     92.1   68.7\n",
       "37       23038    秦杰丽     81.6   73.2\n",
       "38       23039    蒋雯萌     75.9  105.1\n",
       "39       23040    华浩娟     95.3   94.5\n",
       "40       23041    吕林桐     85.3   67.3\n",
       "41       23042    钱楠博     85.8   74.1\n",
       "42       23043    华楠桐     96.1   95.3\n",
       "43       23044    钱萌琪     78.2   89.9\n",
       "44       23045    沈旭玥     84.0   93.4\n",
       "45       23046    施强洋     84.0   81.5\n",
       "46       23047    韩宁骁     74.8   85.9\n",
       "47       23048    尤凯茜     63.6   68.9\n",
       "48       23049    金杰玥     79.3   80.8\n",
       "49       23050    金凯茜     78.8   75.6"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/students_raw_utf8.csv',encoding='utf-8')\n",
    "df\n",
    "df.head()#显示前五条数据\n",
    "df.head(10)#显示前10条数据\n",
    "df.tail(10)#显示后10条数据\n",
    "df.sample(10,random_state=101)\n",
    "print('*'*100)\n",
    "print(df.shape)\n",
    "print('*'*100)\n",
    "print(df.columns)\n",
    "print('*'*100)\n",
    "print(df.info())\n",
    "print('*'*100)\n",
    "print(df.describe())\n",
    "#读数据进行控制\n",
    "df1 = pd.read_csv('./data/students_raw_utf8.csv',\n",
    "                  encoding='utf-8',\n",
    "                  usecols=['student_id','name','Chinese','Math'],\n",
    "                 nrows=50)\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b60ec260-05d7-4d03-8b59-28379ae2fca9",
   "metadata": {},
   "source": [
    "### 1.2、读取不同文件的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "05d3a108-852a-4136-963b-bcaf6358de63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class</th>\n",
       "      <th>head_teacher</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2A</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2B</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3A</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3B</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4A</td>\n",
       "      <td>王老师</td>\n",
       "      <td>4号楼-347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4B</td>\n",
       "      <td>李老师</td>\n",
       "      <td>4号楼-279</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  class head_teacher     room\n",
       "0    2A          孔老师  2号楼-367\n",
       "1    2B          张老师  2号楼-257\n",
       "2    3A          王老师  3号楼-370\n",
       "3    3B          王老师  3号楼-351\n",
       "4    4A          王老师  4号楼-347\n",
       "5    4B          李老师  4号楼-279"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>height_cm</th>\n",
       "      <th>weight_kg</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23001</td>\n",
       "      <td>141.1</td>\n",
       "      <td>43.4</td>\n",
       "      <td>21.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23002</td>\n",
       "      <td>142.5</td>\n",
       "      <td>34.8</td>\n",
       "      <td>17.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23003</td>\n",
       "      <td>140.5</td>\n",
       "      <td>37.9</td>\n",
       "      <td>19.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23004</td>\n",
       "      <td>164.0</td>\n",
       "      <td>47.3</td>\n",
       "      <td>17.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23005</td>\n",
       "      <td>160.2</td>\n",
       "      <td>41.1</td>\n",
       "      <td>16.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>23117</td>\n",
       "      <td>162.7</td>\n",
       "      <td>52.0</td>\n",
       "      <td>19.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>23118</td>\n",
       "      <td>158.7</td>\n",
       "      <td>35.0</td>\n",
       "      <td>13.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>23119</td>\n",
       "      <td>158.6</td>\n",
       "      <td>41.7</td>\n",
       "      <td>16.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>23120</td>\n",
       "      <td>166.7</td>\n",
       "      <td>39.1</td>\n",
       "      <td>14.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>23011</td>\n",
       "      <td>155.3</td>\n",
       "      <td>32.5</td>\n",
       "      <td>13.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>121 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     student_id  height_cm  weight_kg    BMI\n",
       "0         23001      141.1       43.4  21.80\n",
       "1         23002      142.5       34.8  17.14\n",
       "2         23003      140.5       37.9  19.20\n",
       "3         23004      164.0       47.3  17.59\n",
       "4         23005      160.2       41.1  16.01\n",
       "..          ...        ...        ...    ...\n",
       "116       23117      162.7       52.0  19.64\n",
       "117       23118      158.7       35.0  13.90\n",
       "118       23119      158.6       41.7  16.58\n",
       "119       23120      166.7       39.1  14.07\n",
       "120       23011      155.3       32.5  13.48\n",
       "\n",
       "[121 rows x 4 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df_stu = pd.read_csv('./data/students_raw_utf8.csv',encoding='utf-8')\n",
    "df_class = pd.read_csv('./data/class_info_utf8.csv',encoding='utf-8')\n",
    "display(df_class)\n",
    "df_stu\n",
    "df_health = pd.read_excel('./data/students_health_utf8.xlsx')\n",
    "df_health"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "385a91bd-fe23-4157-8b3c-0b4baeded50e",
   "metadata": {},
   "source": [
    "### 1.3、合并数据\n",
    "合并读取的数据\n",
    "pd.merge(A, B, on='共同列名', how='合并方式')\n",
    "| 合并方式      | 含义                |\n",
    "| --------- | ----------------- |\n",
    "| **inner** | 只保留双方都有的记录（交集）    |\n",
    "| **left**  | 保留左表全部，右表能匹配多少算多少 |\n",
    "| **right** | 保留右表全部            |\n",
    "| **outer** | 双方所有记录（并集）        |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "ff4beed0-0cd4-4726-8b8f-b0e271a68aee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df_stu = pd.read_csv('./data/students_raw_utf8.csv',encoding='utf-8')\n",
    "df_class = pd.read_csv('./data/class_info_utf8.csv',encoding='utf-8')\n",
    "# display(df_class)\n",
    "df_stu\n",
    "df_health = pd.read_excel('./data/students_health_utf8.xlsx')\n",
    "df_health\n",
    "#创建一些有问题的数据进行，方便处理\n",
    "test_s = [{'student_id':1111,'name':'测试1'},{'student_id':2323,'name':'测试2'}]\n",
    "df_stu1 = pd.concat([df_stu,pd.DataFrame(test_s)])\n",
    "# display(df_stu1.tail())\n",
    "#通过left进行左连接\n",
    "df = pd.merge(df_stu1,df_class,on='class',how='left')\n",
    "df\n",
    "#连接健康数据\n",
    "df1 = pd.merge(df,df_health,on='student_id',how='left')\n",
    "df1\n",
    "#此时height_cm_x表示原始数据，因为合并的两个字段重复了\n",
    "# df1['height_cm_y'].isna().sum()\n",
    "df1[['height_cm','weight_kg']] = df1[['height_cm_y','weight_kg_x']]\n",
    "df1\n",
    "#再把原始的数据删除\n",
    "df1 = df1.drop(columns=['height_cm_x','height_cm_y','weight_kg_x','weight_kg_y'])\n",
    "df1\n",
    "#处理json数据\n",
    "import json\n",
    "with open('./data/city_region_utf8.json',encoding='utf-8') as f:\n",
    "    city_json = json.load(f)\n",
    "city_json\n",
    "city_lst = list(city_json.items())\n",
    "city_df = pd.DataFrame(city_lst,columns=['city','region'])\n",
    "city_df\n",
    "df_full = pd.merge(df1,city_df,on='city',how='left')\n",
    "df_full\n",
    "df_full.shape\n",
    "#合并完成之后需要进行保存\n",
    "df_full.to_csv('./data/student_full.csv',index=False,encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ecac1a0-e931-4991-a7f9-701af5b4f2ae",
   "metadata": {},
   "source": [
    "### 1.4、合并练习"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "9bef95b4-499f-4882-9844-1e681c679adf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>amount</th>\n",
       "      <th>name_x</th>\n",
       "      <th>price</th>\n",
       "      <th>name_y</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1083</td>\n",
       "      <td>P06</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>2024-01-24</td>\n",
       "      <td>272</td>\n",
       "      <td>沐浴露</td>\n",
       "      <td>55</td>\n",
       "      <td>赵六</td>\n",
       "      <td>昭通</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1029</td>\n",
       "      <td>P08</td>\n",
       "      <td>5</td>\n",
       "      <td>67</td>\n",
       "      <td>2024-01-02</td>\n",
       "      <td>470</td>\n",
       "      <td>拖把</td>\n",
       "      <td>89</td>\n",
       "      <td>赵六</td>\n",
       "      <td>玉溪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1136</td>\n",
       "      <td>P03</td>\n",
       "      <td>4</td>\n",
       "      <td>87</td>\n",
       "      <td>2024-02-08</td>\n",
       "      <td>320</td>\n",
       "      <td>洗发水</td>\n",
       "      <td>39</td>\n",
       "      <td>冯二</td>\n",
       "      <td>zhaotong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1094</td>\n",
       "      <td>P07</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>2024-01-29</td>\n",
       "      <td>115</td>\n",
       "      <td>抽纸</td>\n",
       "      <td>10</td>\n",
       "      <td>钱七</td>\n",
       "      <td>曲靖</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1033</td>\n",
       "      <td>P07</td>\n",
       "      <td>3</td>\n",
       "      <td>34</td>\n",
       "      <td>2024-01-08</td>\n",
       "      <td>395</td>\n",
       "      <td>抽纸</td>\n",
       "      <td>10</td>\n",
       "      <td>冯二</td>\n",
       "      <td>zhaotong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>1077</td>\n",
       "      <td>P01</td>\n",
       "      <td>1</td>\n",
       "      <td>64</td>\n",
       "      <td>2024-01-16</td>\n",
       "      <td>135</td>\n",
       "      <td>牙膏</td>\n",
       "      <td>20</td>\n",
       "      <td>李四</td>\n",
       "      <td>大理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>1126</td>\n",
       "      <td>P03</td>\n",
       "      <td>3</td>\n",
       "      <td>76</td>\n",
       "      <td>2024-02-26</td>\n",
       "      <td>376</td>\n",
       "      <td>洗发水</td>\n",
       "      <td>39</td>\n",
       "      <td>赵六</td>\n",
       "      <td>玉溪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>1139</td>\n",
       "      <td>P09</td>\n",
       "      <td>3</td>\n",
       "      <td>57</td>\n",
       "      <td>2024-01-29</td>\n",
       "      <td>196</td>\n",
       "      <td>扫把</td>\n",
       "      <td>25</td>\n",
       "      <td>冯二</td>\n",
       "      <td>zhaotong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>1077</td>\n",
       "      <td>P10</td>\n",
       "      <td>4</td>\n",
       "      <td>64</td>\n",
       "      <td>2024-01-16</td>\n",
       "      <td>135</td>\n",
       "      <td>垃圾袋</td>\n",
       "      <td>9</td>\n",
       "      <td>李四</td>\n",
       "      <td>大理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>1137</td>\n",
       "      <td>P10</td>\n",
       "      <td>4</td>\n",
       "      <td>74</td>\n",
       "      <td>2024-01-06</td>\n",
       "      <td>232</td>\n",
       "      <td>垃圾袋</td>\n",
       "      <td>9</td>\n",
       "      <td>王五</td>\n",
       "      <td>昆明</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>200 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     order_id product_id  quantity  customer_id  order_date  amount name_x  price name_y      city\n",
       "0        1083        P06         4           60  2024-01-24     272    沐浴露     55     赵六        昭通\n",
       "1        1029        P08         5           67  2024-01-02     470     拖把     89     赵六        玉溪\n",
       "2        1136        P03         4           87  2024-02-08     320    洗发水     39     冯二  zhaotong\n",
       "3        1094        P07         1           42  2024-01-29     115     抽纸     10     钱七        曲靖\n",
       "4        1033        P07         3           34  2024-01-08     395     抽纸     10     冯二  zhaotong\n",
       "..        ...        ...       ...          ...         ...     ...    ...    ...    ...       ...\n",
       "195      1077        P01         1           64  2024-01-16     135     牙膏     20     李四        大理\n",
       "196      1126        P03         3           76  2024-02-26     376    洗发水     39     赵六        玉溪\n",
       "197      1139        P09         3           57  2024-01-29     196     扫把     25     冯二  zhaotong\n",
       "198      1077        P10         4           64  2024-01-16     135    垃圾袋      9     李四        大理\n",
       "199      1137        P10         4           74  2024-01-06     232    垃圾袋      9     王五        昆明\n",
       "\n",
       "[200 rows x 10 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "df_order_items = pd.read_csv('./data/test_data/customer_normal_data/order_items.csv',encoding='utf-8')\n",
    "df_order_items\n",
    "df_orders = pd.read_csv('./data/test_data/customer_normal_data/orders.csv')\n",
    "df_orders\n",
    "df = pd.merge(df_order_items,df_orders,on='order_id',how='left')\n",
    "df\n",
    "df_products = pd.read_csv('./data/test_data/customer_normal_data/products.csv',encoding='utf-8')\n",
    "df = pd.merge(df,df_products,on='product_id',how='left')\n",
    "df\n",
    "df_customers = pd.read_csv('./data/test_data/customer_normal_data/customers.csv',encoding='utf-8')\n",
    "df = pd.merge(df,df_customers,on='customer_id',how='left')\n",
    "df\n",
    "df.to_csv('./data/test_data/customer_normal_data/full.csv',encoding='utf-8-sig',index=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "83a43d5f-2914-494a-b75a-aae5c9bf0748",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "df = pd.read_csv('./data/test_data/customer_normal_data/full.csv',encoding='utf-8')\n",
    "df\n",
    "#重新命名name\n",
    "df = df.rename(columns={'name_x':'product_name','name_y':'cname'})\n",
    "df\n",
    "# display(df)\n",
    "#处理每个商品的金额\n",
    "df['amount'] = df['price']*df['quantity']\n",
    "df.sort_values(by='order_id')\n",
    "order_amount = df.groupby('order_id',as_index=False)['amount'].sum().rename(columns={'amount':'order_amount'})\n",
    "order_amount\n",
    "#如果非要把数据弄到full中，再进行一次合并即可\n",
    "df = pd.merge(df,order_amount,on='order_id',how='left')\n",
    "df.sort_values(by='order_id')\n",
    "df.to_csv('./data/test_data/customer_normal_data/full.csv',index=False,encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4d8a5bf-71a6-4bab-b8c0-19233482ead5",
   "metadata": {},
   "source": [
    "## 2、数据的处理\n",
    "### 2.1、缺失值的处理\n",
    "#### 2.1.1、查看分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "9d3c96dd-66af-47a5-840b-5ed4396dc8ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "#1、查看数据的缺失情况\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "#2、会发现一直都有两个数据是缺失的\n",
    "#通过代码来获取缺失的值数量\n",
    "df['na_count'] = df.isna().sum(axis=1)\n",
    "df\n",
    "#此时可以通过布尔索引将na_count大于10条的删除\n",
    "df = df[df['na_count']<=10]\n",
    "df = df.drop(columns=['na_count'])\n",
    "df\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "#对数据进行去重\n",
    "df['student_id'].duplicated().sum()\n",
    "#查询哪些数据重复\n",
    "df[df['student_id'].duplicated()]\n",
    "#去掉重复，保留第一个\n",
    "df = df.drop_duplicates(subset=['student_id'],keep='first')\n",
    "df['student_id'].duplicated().sum()\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8071777a-de39-4856-bcd1-fa2eebfb40a9",
   "metadata": {},
   "source": [
    "#### 2.1.2、city的缺失值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "a939b46f-5d6d-4f15-9746-5ef12a18c55e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "#1、查看数据的缺失情况\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "#1.1、去空格，添加title\n",
    "df['city'] = df['city'].astype(str).str.strip().str.title()\n",
    "df\n",
    "#1.2、处理NaN问题，将所有的空字符转换为标准的nan\n",
    "df['city'].unique()\n",
    "#将空进行标准化\n",
    "df['city'] = df['city'].replace(['None','Nan','NaN','Null','NULL',' '],np.nan)\n",
    "df['city'].unique()\n",
    "#1.3、统一标识，使用汉字标识\n",
    "df['city'] = df['city'].replace({'Zhaotong':'昭通','Kunming':'昆明'})\n",
    "df['city'].unique()\n",
    "#此时region已经无意义，因为已经修改了，此时需要重新合并\n",
    "df = df.drop(columns=['region'])\n",
    "df\n",
    "#进行区域的重新合并\n",
    "import json\n",
    "with open('./data/city_region_utf8.json',encoding='utf-8') as f:\n",
    "    city_json = json.load(f)\n",
    "city_df = pd.DataFrame(list(city_json.items()),columns=['city','region'])\n",
    "#合并\n",
    "df = pd.merge(df,city_df,on='city',how='left')\n",
    "df\n",
    "#1.4、填充city数据\n",
    "df['city'] = df['city'].fillna('未知')\n",
    "df['region'] = df['region'].fillna('未知区域')\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9aa69606-bbac-41ea-a880-f54e1595edaf",
   "metadata": {},
   "source": [
    "#### 2.1.3、club的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "11a41b5e-465d-4d5e-9b60-9654dc7ccec3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Math             9\n",
       "Chinese          6\n",
       "absence_count    1\n",
       "name             0\n",
       "student_id       0\n",
       "age              0\n",
       "class            0\n",
       "gender           0\n",
       "city             0\n",
       "club             0\n",
       "English          0\n",
       "Physics          0\n",
       "register_date    0\n",
       "Chemistry        0\n",
       "Biology          0\n",
       "head_teacher     0\n",
       "room             0\n",
       "BMI              0\n",
       "height_cm        0\n",
       "weight_kg        0\n",
       "region           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "#1、查看数据的缺失情况\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "#2、社团中的值的类型是否符合要求\n",
    "df['club'].unique()\n",
    "#3、处理nan\n",
    "df['club'] = df['club'].replace(['None','Nan','NaN','Null','NULL',' '],np.nan)\n",
    "df\n",
    "#4、可以不用管，将来机器学习的更好做，也可以替换成无社团\n",
    "df['club'] = df['club'].fillna('无社团')\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8')\n",
    "df.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efbac1a6-0f55-4bd7-828b-eff30505dd5b",
   "metadata": {},
   "source": [
    "#### 2.1.4、成绩的处理\n",
    "成绩的处理，一定要先处理异常值，处理的逻辑是先把异常值直接设置nan，之后再来进行缺省值的处理\n",
    "- 异常值的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "cc00d10d-c99a-4c46-9a3c-4baccbfda502",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df['student_id'] = df['student_id'].astype(int)\n",
    "df.loc[3,'Chinese'] = 199\n",
    "df.loc[1,'Math'] = -12\n",
    "df.loc[2,'Physics'] = 233\n",
    "df.loc[2,'Chemistry'] = -9\n",
    "scores = ['Chinese','Math','English','Physics','Chemistry','Biology']\n",
    "bad_v = (df[scores]<0)|(df[scores]>100)\n",
    "df[scores][bad_v]\n",
    "# df.loc[(df['Math']<0)|(df['Math']>100),'Math'] = np.nan\n",
    "# df\n",
    "#可以通过循环的方式来遍历\n",
    "for s in scores:\n",
    "    df.loc[(df[s]<0)|(df[s]>100),s] = np.nan\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "#把身高和体重一次性处理掉\n",
    "df.loc[(df['height_cm']<100)|(df['height_cm']>190),'height_cm'] = np.nan\n",
    "df.loc[(df['weight_kg']<20)|(df['weight_kg']>120),'weight_kg'] = np.nan\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "467d88b1-834b-47cc-891f-37d03b690549",
   "metadata": {},
   "source": [
    "成绩是典型的 数值型数据（numeric features）。对于数值缺失，有三种常见的填充策略：\n",
    "| 填充方式                     | 优点            | 缺点            | 是否适合成绩？   |\n",
    "| ------------------------ | ------------- | ------------- | --------- |\n",
    "| **全局平均值填充**              | 简单            | 混合不同班级，不准确    | ❌ 不推荐     |\n",
    "| **按班级平均值填充（class mean）** | 同班学业水平接近，非常合理 | 如果班级人数太少，会不稳定 | ✔ 推荐（最合理） |\n",
    "| **按科目中位数填充**             | 稳定，不受极端值影响    | 忽略班级结构        | ✔ 可作为备用方案 |4\n",
    "\n",
    "- tranform的使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "ff3de719-7fe4-4eaa-b82f-496d46038de2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class</th>\n",
       "      <th>scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  class  scores\n",
       "0     A    80.0\n",
       "1     A    90.0\n",
       "2     B    80.0\n",
       "3     B    80.0\n",
       "4     A    85.0"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.DataFrame({\n",
    "    'class':['A','A','B','B','A'],\n",
    "    'scores':[80,90,np.nan,80,np.nan]\n",
    "}) \n",
    "\n",
    "# df\n",
    "# df.groupby('class')['scores'].mean()\n",
    "# print(df.groupby('class')['scores'].mean())\n",
    "# print(df.groupby('class')['scores'].transform('mean'))\n",
    "# #transform('mean')是在调用函数mean,参数是每一列的分组信息\n",
    "# df['class_mean'] = df.groupby('class')['scores'].transform('mean')\n",
    "# df\n",
    "# df['scores'] = df['scores'].fillna(df['class_mean'])\n",
    "# df = df.drop(columns=['class_mean'])\n",
    "# df\n",
    "\n",
    "#以上操作太麻烦了，所有在transform中直接通过函数处理\n",
    "# def fn(X):\n",
    "#     return X.fillna(X.mean())\n",
    "# df['scores'] = df.groupby('class')['scores'].transform(fn)\n",
    "# df\n",
    "#可以直接使用匿名函数处理\n",
    "df['scores'] = df.groupby('class')['scores'].transform(\n",
    "    lambda X:X.fillna(X.mean())\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d58fac49-705a-4b01-bc5b-33a37623a016",
   "metadata": {},
   "source": [
    "- 成绩的缺失值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "84d310a5-62fb-4dc8-908c-c7fd2766565a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "weight_kg        1\n",
       "absence_count    1\n",
       "gender           0\n",
       "name             0\n",
       "student_id       0\n",
       "age              0\n",
       "class            0\n",
       "register_date    0\n",
       "city             0\n",
       "Math             0\n",
       "English          0\n",
       "club             0\n",
       "Chinese          0\n",
       "Chemistry        0\n",
       "Physics          0\n",
       "head_teacher     0\n",
       "Biology          0\n",
       "room             0\n",
       "BMI              0\n",
       "height_cm        0\n",
       "region           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "scores = ['Chinese','Math','English','Physics','Chemistry','Biology']\n",
    "\n",
    "# df['Math'] = df.groupby('class')['Math'].transform(\n",
    "#     lambda x:x.fillna(x.mean().round(2))\n",
    "# )\n",
    "# df\n",
    "for s in scores:\n",
    "    df[s] = df.groupby('class')[s].transform(\n",
    "        lambda x:x.fillna(x.mean().round(2))\n",
    "    )\n",
    "df\n",
    "df.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2a18aa5-43e3-44fc-b27e-c97080c5f541",
   "metadata": {},
   "source": [
    "#### 2.1.5、其他缺失值的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "681e86ae-2e0d-4085-a3fb-01b73f1216ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "#缺勤数，为空一般来说就是没有缺席\n",
    "df['absence_count'] = df['absence_count'].fillna(0).astype(int)\n",
    "df\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "df['height_cm'] = df.groupby('class')['height_cm'].transform(\n",
    "    lambda x:x.fillna(x.mean())\n",
    ")\n",
    "df['weight_kg'] = df.groupby('class')['weight_kg'].transform(\n",
    "    lambda x:x.fillna(x.mean())\n",
    ")\n",
    "df.isna().sum().sort_values(ascending=False)\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3039ec8e-a3c4-462b-9e18-e7c4c96537d2",
   "metadata": {},
   "source": [
    "#### 2.1.7、作业\n",
    "- Part 1：数据加载与初步检查\n",
    "使用 Pandas 读取四张表。\n",
    "分别打印：行数、列数、列名（columns），缺失值统计（isna().sum()）\n",
    "- Part 2：Customers 表清洗\n",
    "    - 2.1 删除重复行\n",
    " \n",
    "      使用 duplicated() 找到重复顾客记录。使用 drop_duplicates() 删除。\n",
    "\n",
    "    - 2.2 清洗 name 字段\n",
    " \n",
    "      去除前后空格（str.strip()）\n",
    " \n",
    "      统计空姓名（name == \"\" 或 NaN）\n",
    "\n",
    "    - 2.3 清洗 age 字段\n",
    "\n",
    "      找出年龄异常值：age < 0,age > 120,age 为 NaN\n",
    " \n",
    "      处理方式：异常值 → 填为 NaN\n",
    " \n",
    "      最终用 年龄中位数（median） 填充缺失值。\n",
    "\n",
    "    - 2.4 清洗 city 字段\n",
    " \n",
    "      把 \"\", \"None\", \"nan\" → 统一替换为 NaN\n",
    " \n",
    "      用该列的 众数（mode） 填充缺失值\n",
    " \n",
    "- Part 3：Products 表清洗\n",
    "    - 3.1 清洗 price找到并处理：价格 < 0，价格 > 100000，价格为 NaN，清洗后使用 中位数 填充缺失值。\n",
    "\n",
    "    - 3.2 清洗产品名称：名称为空字符串（\"\"）的 → 填为 \"Unknown Product\"\n",
    "\n",
    "- Part 4：Orders 表清洗\n",
    "    - 4.1 清洗 customer_id\n",
    "\n",
    "        无效情况包括：空字符串，NaN，不存在于 customers 表中的 customer_id,处理方式：删除这些订单行\n",
    "\n",
    "    - 4.2 清洗 order_date\n",
    "    \n",
    "      用 to_datetime(errors='coerce') 将非法日期转换为 NaN,删除日期为 NaN 的订单,最终要求日期列为 datetime 类型\n",
    " \n",
    "- Part 5：Order Items 清洗\n",
    "    - 5.1 清洗 product_id\n",
    "\n",
    "      删除以下情况：不在 products 表中,\"P999\"\n",
    "\n",
    "    - 5.2 清洗 quantity\n",
    "\n",
    "      非法情况：0,负数,NaN,处理方式：将这些值设为 NaN,再用 1 填充（最小购买数量）\n",
    " \n",
    "- Part 6：多表合并\n",
    "\n",
    "    - 按顺序合并成最终表 final_df：order_items 合并 products,合并 orders,合并 customers\n",
    "\n",
    "    - 注意事项：\n",
    " \n",
    "      要保证 merge 不会产生无意义的数据\n",
    " \n",
    "      合并方式合理选择（inner / left）\n",
    " \n",
    "      最终表应包含：顾客信息,产品信息,订单信息,订单数量,金额字段：\n",
    " \n",
    "    - 根据数据情况看是否需要进行二次清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5b092ea-1fa9-4d21-8e81-b4177df2663d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
